



import java.io.File;
 import java.io.FileInputStream;
import java.util.Hashtable;
 import java.util.Iterator;
import java.util.Properties;
import java.util.Vector;


import org.apache.poi.ss.usermodel.DateUtil;
 import org.apache.poi.ss.usermodel.WorkbookFactory;
 import org.apache.poi.ss.usermodel.Workbook;
 import org.apache.poi.ss.usermodel.Sheet;
 import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;



 public class ss {

     /**
	 * 
	 */
	public static File inputFile = null;
	public	static FileInputStream fileIStream = null;
	public  Workbook workbook = null;
	public	 Sheet sheet = null;
	public	static Row row = null;
	public	static Cell cell = null;
	public	static String excelvalue = null;
	public	static Iterator<Row> rowIterator = null;
	public	static Iterator<Cell> cellIterator = null;
	public	static int numSheets = 0;
	public	 int columncount,rowcount;
	public	static String columnvalue[][];
	public	static   Hashtable columnshashtable;
	public static   Vector columnVector;
	public Properties mapProperties;

	public ss() {

	
		columnVector = new Vector();
		mapProperties = new Properties();
		// TODO Auto-generated constructor stub
	}

	/**
      * @param args the command line arguments
      */
	
     /*public static void main(String[] args) {
    	 
    	
        
         try {
             inputFile = new File("E:/source codes/trial/Sampleexcel.xls");
             fileIStream = new FileInputStream(inputFile);
             // Use the WorkbookFactory create() factory method to get
             // either an HSSFWorkbook or XSSFWorkbook depending on
             // the file type wrapped in the FileInputStream
             workbook = WorkbookFactory.create(fileIStream);
           
             
             // Lets see what we got, an XSSF or HSSFWorkbook
             if(workbook instanceof
 org.apache.poi.hssf.usermodel.HSSFWorkbook) {
                 System.out.println("You got an HSSFWorkbook");
             }
             else {
                 System.out.println("You got an XSSFWorkbook");
             }
             // Get the number of sheets
             numSheets = workbook.getNumberOfSheets();
             // Iterate through each sheet
             // Get the sheet
             sheet = workbook.getSheet("Sheet1");// .getSheetAt(i);
             columnvalue=new String [sheet.getLastRowNum()][2000];
             //commented for only specified sheet and calling readcolumnxlsxname
             ss s=new ss();
             s.readxlsxcolumn(sheet);
             if(checkxlsxcolumn("requestD"))
             {
            	 System.out.println("column Exist");
             }
             else
             {
            	 System.out.println("column Doesn't Exist");
             }
           s.readeachcolumn();
           s.displayxlsx();
             
             for(int i = 0; i < numSheets; i++) {
                 // Get the sheet
                 sheet = workbook.getSheet("Sheet1");// .getSheetAt(i);
                
                 
                 // Get an iterator to work through the rows on the sheet
                 rowIterator = sheet.iterator();
                 // Iterate through the rows
                 if(rowIterator.hasNext()) {
                     row = rowIterator.next();
                     System.out.println("Processing row number: " + row.getRowNum());
                     // Get an iterator to work through the cells on therow
                     cellIterator = row.iterator();
                     // Iterate through the cells
                     while(cellIterator.hasNext()) {
                         cell = cellIterator.next();
                         // Just print out the num,ber of the cell
                         // and the String representatin of the instance.
                         if(cell.getCellType()==Cell.CELL_TYPE_STRING)
                         {excelvalue=cell.getRichStringCellValue().getString();
                         //System.out.println("Cell: " +cell.getRichStringCellValue().getString());
                         }
                         if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
                        	 if (DateUtil.isCellDateFormatted(cell)) {
                        		 excelvalue=cell.getDateCellValue().toString();
                                // System.out.println("Cell: "+cell.getDateCellValue().toString());
                             } else {
                            	 excelvalue=Double.toString(cell.getNumericCellValue());
                                 //System.out.println("Cell: "+Double.toString(cell.getNumericCellValue()));
                             }
                         }
                         if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN)
                         {excelvalue=Boolean.toString(cell.getBooleanCellValue());
                        	// System.out.println("Cell: " +cell.getBooleanCellValue());
                         
                         }
                         if(cell.getCellType()==Cell.CELL_TYPE_FORMULA)
                         {excelvalue=cell.getCellFormula().toString();
                        	 //System.out.println("Cell: " +cell.getCellFormula().toString());
                         
                         }
                         
                        System.out.println(excelvalue);
                     }
                 }
             }
             
         }
         catch(Exception ex) {
             System.out.println("Caught an: " + ex.getClass().getName());
             System.out.println("Message: " + ex.getMessage());
             System.out.println("Stacktrace follows.........");
             ex.printStackTrace(System.out);
         }
         finally {
             if(fileIStream != null) {
                 try {
                    fileIStream.close();
                 }
                 catch(Exception ex) {
                     // I G N O R E //
                 }
             }
         }
     }*/
     
    /* public  void displayxlsx()
     {  rowIterator = sheet.iterator();
     // Iterate through the rows
     while(rowIterator.hasNext()) {
    	 int i=0;
         row = rowIterator.next();
    	 for( i=0;i<columnshashtable.size();i++){
    		//System.out.println(columnVector.elementAt(i)+" = "+columnshashtable.get(columnVector.elementAt(i)));
    		 System.out.println(columnVector.elementAt(i)+" = "+columnshashtable.get(columnVector.elementAt(i)));
    		//System.out.println("requesID= "+(String)columnshashtable.get("requestID"));
    		}
     }
     }*/
     
     
     public  Hashtable readeachcolumn( Iterator rowIterator)
     {
    	 
         int count = 0;
         columnshashtable = new Hashtable();
         // Get an iterator to work through the rows on the sheet
         //rowIterator = sheet.iterator();
         // Iterate through the rows
         if(rowIterator.hasNext()) {
        	 int i=0;
             row = (Row) rowIterator.next();
             System.out.println("Processing row number: " + row.getRowNum());
             // Get an iterator to work through the cells on the row
             cellIterator = row.iterator();
             // Iterate through the cells
             while(cellIterator.hasNext()) {
                 cell = cellIterator.next();
                 cell.setCellType(1);
                 // Just print out the number of the cell
                 // and the String representation of the instance.
                 if(cell.getCellType()==Cell.CELL_TYPE_STRING)
                 {excelvalue=cell.getRichStringCellValue().getString();
                 //System.out.println("Cell: " +cell.getRichStringCellValue().getString());
                 }
                 if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
                	 if (DateUtil.isCellDateFormatted(cell)) {
                		 excelvalue=cell.getDateCellValue().toString();
                        // System.out.println("Cell: "+cell.getDateCellValue().toString());
                     } else {
                    	 excelvalue=Double.toString(cell.getNumericCellValue());
                         //System.out.println("Cell: "+Double.toString(cell.getNumericCellValue()));
                     }
                 }
                 if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN)
                 {excelvalue=Boolean.toString(cell.getBooleanCellValue());
                	// System.out.println("Cell: " +cell.getBooleanCellValue());
                 
                 }
                 if(cell.getCellType()==Cell.CELL_TYPE_FORMULA)
                 {excelvalue=cell.getCellFormula().toString();
                	 //System.out.println("Cell: " +cell.getCellFormula().toString());
                 
                 } System.out.println(excelvalue);
                 columnshashtable.put(columnVector.elementAt(i), excelvalue);i++;
               
             }
         }return columnshashtable;
     }
     
          
     public  void readxlsxcolumn(Sheet sheet )
     {
    	 
         
         
         // Get an iterator to work through the rows on the sheet
         rowIterator = sheet.iterator();
         rowcount=sheet.getLastRowNum();
         // Iterate through the rows
         while(rowIterator.hasNext()) {
             row = rowIterator.next();
             //System.out.println("Processing row number: " + row.getRowNum());
             // Get an iterator to work through the cells on the row
             cellIterator = row.iterator();
             // Iterate through the cells
             while(cellIterator.hasNext()) {
                 cell = cellIterator.next();
                 cell.setCellType(1);
                 // Just print out the number of the cell
                 // and the String representation of the instance.
                 if(cell.getCellType()==Cell.CELL_TYPE_STRING)
                 {excelvalue=cell.getRichStringCellValue().getString();
                 //System.out.println("Cell: " +cell.getRichStringCellValue().getString());
                 }
                 if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
                	 if (DateUtil.isCellDateFormatted(cell)) {
                		 excelvalue=cell.getDateCellValue().toString();
                        // System.out.println("Cell: "+cell.getDateCellValue().toString());
                     } else {
                    	 excelvalue=Double.toString(cell.getNumericCellValue());
                         //System.out.println("Cell: "+Double.toString(cell.getNumericCellValue()));
                     }
                 }
                 if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN)
                 {excelvalue=Boolean.toString(cell.getBooleanCellValue());
                	// System.out.println("Cell: " +cell.getBooleanCellValue());
                 
                 }
                 if(cell.getCellType()==Cell.CELL_TYPE_FORMULA)
                 {excelvalue=cell.getCellFormula().toString();
                	 //System.out.println("Cell: " +cell.getCellFormula().toString());

                 
                 }
                 columncount++;
                this.columnVector.add(excelvalue);
               //System.out.println("column "+columncount+"="+excelvalue/*+"\tno="+ columnVector.size()+cell.CELL_TYPE_STRING*/);
             }
         }
     }
    
     
     public  Boolean checkxlsxcolumn(String columnname,Sheet sheet)
     {
    	 
         
         
         // Get an iterator to work through the rows on the sheet
         rowIterator = sheet.iterator();
         // Iterate through the rows
         if(rowIterator.hasNext()) {
             row = rowIterator.next();
             //System.out.println("Processing row number: " + row.getRowNum());
             // Get an iterator to work through the cells on the row
             cellIterator = row.iterator();
             // Iterate through the cells
             while(cellIterator.hasNext()) {
                 cell = cellIterator.next();
                 // Just print out the number of the cell
                 // and the String representation of the instance.
                 if(cell.getCellType()==Cell.CELL_TYPE_STRING)
                 {excelvalue=cell.getRichStringCellValue().getString();
                 //System.out.println("Cell: " +cell.getRichStringCellValue().getString());
                 }
                 if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
                	 if (DateUtil.isCellDateFormatted(cell)) {
                		 excelvalue=cell.getDateCellValue().toString();
                        // System.out.println("Cell: "+cell.getDateCellValue().toString());
                     } else {
                    	 excelvalue=Double.toString(cell.getNumericCellValue());
                         //System.out.println("Cell: "+Double.toString(cell.getNumericCellValue()));
                     }
                 }
                 if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN)
                 {excelvalue=Boolean.toString(cell.getBooleanCellValue());
                	// System.out.println("Cell: " +cell.getBooleanCellValue());
                 
                 }
                 if(cell.getCellType()==Cell.CELL_TYPE_FORMULA)
                 {excelvalue=cell.getCellFormula().toString();
                	 //System.out.println("Cell: " +cell.getCellFormula().toString());
                 
                 }
               if(excelvalue.equals(columnname))
            	   return true;
               // System.out.println(excelvalue);
             }
         }
         return false;
     }
  
     
 }